import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class DBManager {
	Connection conn;
	public DBManager(String connStr) throws SQLException {
		connect(connStr);
	}
	private void connect(String connStr) throws SQLException {
		conn = DriverManager.getConnection(connStr);
	}
	public void close() {
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public ResultSet execute(String sqlStmt) {
		ResultSet rs=null;
		try {
			final Statement stmt= conn.createStatement(
					ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			stmt.execute(sqlStmt);
			rs=stmt.getResultSet();
//			while(rs.next()) {
//				System.err.println(rs.getObject(1));
//			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return rs;
	}
	
	public ResultSet execute(String sqlStmt, Object... args) {
		ResultSet rs=null;
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(
					sqlStmt, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			for(int i=0; i<args.length; i++) {
				ps.setObject(i+1, args[i]);
			}
			ps.execute();
			rs=ps.getResultSet();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	
	//[ test =====================================================
	private static void connectDatabase() {
		try {
			//final Connection conn = DriverManager.getConnection("jdbc:derby:studentlist;create=true");
			final Connection conn = DriverManager.getConnection("jdbc:derby:studentlist");
			
			

			importData(conn);
			//exportData(conn);
			print(conn);
			
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	private static void print(Connection conn) {
		Statement stmt;
		try {
			stmt = conn.createStatement();
			stmt.execute("Select * from students");
			final ResultSet rs=stmt.getResultSet();
			while(rs.next()) {
				System.err.print(rs.getInt(1)+", ");
				System.err.print(rs.getString(2)+", ");
				System.err.println(rs.getString(3));
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	private static void loadDatabaseDriver() {
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}	
	private static void importData(Connection conn) {
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(
				"CALL SYSCS_UTIL.SYSCS_IMPORT_DATA(null, 'STUDENTS', null, '2,3,8', ?, null, null, 'ms950', ?)");
			ps.setString(1, "slist_utf8.csv");
			ps.setInt(2, 1); //replace mode
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	private static void exportData(Connection conn) {
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(
				"CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY('select * from test',?, null, null, null)");
			ps.setString(1, "test.del");
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		loadDatabaseDriver();		
		connectDatabase();
	}
	
	
//derby import/export commands:	
//	CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY
//	CALL SYSCS_UTIL.SYSCS_IMPORT_DATA 
	/*
	 * CALL SYSCS_UTIL.SYSCS_IMPORT_DATA
> (null, 'students', null, '2,3,8', 'slist.csv', null, null, null, 0);
	 * */
}
